package com.mp.approvalreq.dao.impl;

import com.mp.approvalreq.dao.AppendSignDao;
import com.mp.approvalreq.entity.AppendSign;
import com.plusmoney.util.SQLLogger;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class AppendSignDaoImpl extends AbstractDao implements AppendSignDao {

    private static final Logger logger = LoggerFactory.getLogger(AppendSignDaoImpl.class);

    private Connection connection;

    public AppendSignDaoImpl(Connection connection) {
        this.connection = connection;
    }

    private PreparedStatement getPreparedStatement(String sql) throws SQLException {
        return connection.prepareStatement(sql);
    }

    @Override
    String getQuerySql(String condition) {
        final String query = "select id, company_id, company_info_id, request_id, date,"
                + " sign_type, sign_time, reason, schedule_name, work_time_range"
                + " from sys_approval_append_sign";
        return String.format("%s %s", query, condition);
    }

    @Override
    AppendSign getObject(ResultSet rs) throws SQLException {
        AppendSign appendSign = new AppendSign();
        appendSign.setId(rs.getInt(1));
        appendSign.setCompanyId(rs.getInt(2));
        appendSign.setCompanyInfoId(rs.getInt(3));
        appendSign.setRequestId(rs.getInt(4));
        appendSign.setDate(rs.getString(5));

        appendSign.setSignType(rs.getInt(6));
        appendSign.setSignTime(rs.getTimestamp(7));
        appendSign.setReason(rs.getString(8));
        appendSign.setScheduleName(rs.getString(9));
        appendSign.setWorkTimeRange(rs.getString(10));
        return appendSign;
    }

    @Override
    public void insertBatchAppendSigns(List<AppendSign> appendSigns) throws SQLException {
        String sql = "insert into sys_approval_append_sign set"
                + " company_id = ?, company_info_id = ?, request_id = ?, date = ?, sign_type = ?,"
                + " sign_time = ?, reason = ?, schedule_name = ?, work_time_range = ?, approval_num = ?,"
                + " create_time = now()";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            for (AppendSign appendSign : appendSigns) {
                ps.setInt(1, appendSign.getCompanyId());
                ps.setInt(2, appendSign.getCompanyInfoId());
                ps.setInt(3, appendSign.getRequestId());
                ps.setString(4, appendSign.getDate());
                ps.setInt(5, appendSign.getSignType());

                ps.setTimestamp(6, new Timestamp(appendSign.getSignTime().getTime()));
                ps.setString(7, appendSign.getReason());
                ps.setString(8, appendSign.getScheduleName());
                ps.setString(9, appendSign.getWorkTimeRange());
                ps.setString(10, appendSign.getApprovalNum());
                logger.info(SQLLogger.logSQL(sql, appendSign.getCompanyId(), appendSign.getCompanyInfoId(),
                        appendSign.getRequestId(), appendSign.getDate(), appendSign.getSignType(), appendSign.getSignTime(),
                        appendSign.getReason(), appendSign.getScheduleName(), appendSign.getWorkTimeRange(), appendSign.getApprovalNum()));

                ps.addBatch();
            }
            ps.executeBatch();
        }
    }

    @Override
    public void deleteAppendSignByRequestId(Integer requestId, Integer companyId) throws SQLException {
        String sql = "update sys_approval_append_sign set state = 2, update_time = now() where request_id = ? and company_id = ?";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, requestId);
            ps.setInt(2, companyId);
            logger.info(SQLLogger.logSQL(sql, requestId, companyId));

            ps.executeUpdate();
        }
    }

    @Override
    public List<AppendSign> selectAppendSignsByRequestId(Integer requestId) throws SQLException {
        List<AppendSign> list = new ArrayList<>();
        String sql = getQuerySql("where request_id = ?");

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, requestId);
            logger.info(SQLLogger.logSQL(sql, requestId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(getObject(rs));
                }
            }
        }
        return list;
    }

    @Override
    public Map<Integer, Integer> selectUserAppendSignsTimesMapByMonth(Integer companyId, Integer companyInfoId, String month) throws SQLException {
        Map<Integer, Integer> map = new HashMap<>();
        String sql = "select t.Fancestor_id, count(*) from request_flow f"
                + " left join sys_approval_append_sign a on f.Frequest_id = a.request_id"
                + " left join request_template t on t.Ftemplate_id = f.Frequest_templet"
                + " where a.company_id = ? and a.company_info_id = ? and date_format(f.Fcreate_time,'%Y-%m') = ? and a.state = 1"
                + " group by t.Fancestor_id";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            ps.setInt(2, companyInfoId);
            ps.setString(3, month);
            logger.info(SQLLogger.logSQL(sql, companyId, companyInfoId, month));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    map.put(rs.getInt(1), rs.getInt(2));
                }
            }
        }
        return map;
    }

    @Override
    public Map<Integer, List<AppendSign>> selectAppendSignsByRequestIds(String requestIds) throws SQLException {
        Map<Integer, List<AppendSign>> map = new HashMap<>();
        if (StringUtils.isEmpty(requestIds)) {
            return map;
        }
        String sql = getQuerySql("where request_id in(" + requestIds + ")");

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            logger.info(sql);

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    AppendSign appendSign = getObject(rs);
                    if (!map.containsKey(appendSign.getRequestId())) {
                        map.put(appendSign.getRequestId(), new ArrayList<>());
                    }
                    map.get(appendSign.getRequestId()).add(appendSign);
                }
            }
        }
        return map;
    }

    @Override
    public List<AppendSign> selectCompanyAppendSignsByDateScope(Integer companyId, String startTime, String endTime) throws SQLException {
        List<AppendSign> list = new ArrayList<>();
        String sql = getQuerySql("where company_id = ? and sign_time >= ? and sign_time <= ?");
        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            ps.setString(2, startTime);
            ps.setString(3, endTime);
            logger.info(SQLLogger.logSQL(sql, companyId, startTime, endTime));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(getObject(rs));
                }
            }
        }
        return list;
    }

    @Override
    public List<AppendSign> selectBatchUserAppendSignsByDateScope(Integer companyId, String companyInfoIds, String startTime, String endTime) throws SQLException {
        List<AppendSign> list = new ArrayList<>();
        String sql = getQuerySql("where company_id = ? and company_info_id in(" + companyInfoIds + ") and sign_time >= ? and sign_time <= ?");
        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            ps.setString(2, startTime);
            ps.setString(3, endTime);
            logger.info(SQLLogger.logSQL(sql, companyId, startTime, endTime));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(getObject(rs));
                }
            }
        }
        return list;
    }

    @Override
    public Date selectCreateTimeByRequestId(Integer requestId) throws SQLException {
        String sql = "select create_time from sys_approval_append_sign where request_id = ? limit 1";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, requestId);
            logger.info(SQLLogger.logSQL(sql, requestId));

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return rs.getTimestamp(1);
                }
            }
        }
        return null;
    }
}
